<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta name="description" content="learn javascript by www.liaoxuefeng.com">
  <title>Sheet</title>
  <link rel="stylesheet" type="text/css" href="https://cdn.bootcss.com/bootstrap/3.3.5/css/bootstrap.min.css">
  <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"></script>
	<script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script src="https://cdn.bootcss.com/vue/2.5.2/vue.min.js"></script>
  <script src="https://cdn.bootcdn.net/ajax/libs/vue-resource/1.5.1/vue-resource.js"></script>

  <script src="makeXls.js"></script>
  <style>
    #sheet {
      /*table-layout: fixed; 让每一列等宽*/
      min-width: auto;
      margin-bottom: 0px;
      user-select: none;
    }

    #sheet tr>th {
      min-width: 80px;
      background-color: #f4f4f4;
    }

    #sheet tr>th:first-child {
      width: 30px;
    }

    #sheet tr>td {
      min-width: 80px;
      white-space: nowrap;
      word-wrap: normal;
      white-space: nowrap;
    }

    #sheet tr>td:first-child {
      width: 50px;
      background-color: #f4f4f4;
    }

    #sheet tr>td.selecting {
      background: #ace;
    }

    html, body, #app {
      height: 100%;
    }

    #app {
      display: flex;
      flex-direction: column;
    }

    #header {
      margin-bottom: 0;
    }

    #footer {
      background-color: #e7e7e7;
      height: 30px;
      min-height: 30px;
      overflow: hidden;
      margin-bottom: 0;
    }

    #main {
      flex: auto;
      overflow: auto;
    }

    #main::-webkit-scrollbar {
      width: 8px;
      height: 8px;
      background-color: transparent;
    }

    #main::-webkit-scrollbar-thumb {
      border-radius: 5px;
      background-color: #abb5c0;
      color: black
    }

    #toolbar button:focus {
      outline: none;
    }
  </style>
  <script>

    var ID = '135';
    var COLUMNS = 10, ROWS = 100;

    function createHeader() {
      var hdr = [{ row: 0, col: 0 }];
      for (var i = 1; i <= COLUMNS; i++) {
        hdr.push({
          row: 0,
          col: i,
          text: String.fromCharCode(64 + i),
          contenteditable: true,
        });
      }
      return hdr;
    }

    function createRow(rIndex) {
      var row = [];
      for (var i = 0; i <= COLUMNS; i++) {
        row.push({
          row: rIndex,
          col: i,
          contenteditable: true,
          text: '',
          align: 'left'
        });
      }
      row[0].text = '' + rIndex;
      return row;
    }

    function createRows() {
      var rows = [];
      for (var i = 1; i <= ROWS; i++) {
        rows.push(createRow(i));
      }
      return rows;
    }

    function isBetween(x, a, b) {
      var max = Math.max(a, b);
      var min = Math.min(a, b);
      return min <= x && x <= max;
    }

    function loadData(key, context, isJson) {
      var data = sessionStorage.getItem(key);
      //console.log(data);
      if (data !== null) {
        if (isJson) {
          data = JSON.parse(data);
        }
        context[key] = data;
      }
    }

    function saveData(key, context, isJson) {
      var data = context[key];
      if (isJson) {
        data = JSON.stringify(data);
      }
      sessionStorage.setItem(key, data);
    }

    $(function () {
      var vm = new Vue({
        el: '#app',
        data: {
          title: document.title,
          header: createHeader(),
          rows: createRows(),
          selectedRowIndex: 0,
          selectedColIndex: 0,
          // 单元格选择
          isSelecting: false,
          selectingFrom: [1, 1],
          selectingTo: [1, 1],
        },
        methods: {
          load: function () {
            /*
            var that = this;
            that.$resource('/api/sheets/' + ID).get().then(function (resp) {
              resp.json().then(function (result) {
                that.title = result.title;
                that.rows = result.rows;
              });
            }, function (resp) {
              alert('Failed to load.');
            });
            */
            loadData('title', this);
            loadData('header', this, true);
            loadData('rows', this, true);
          },
          save: function () {
            /*
            this.$resource('/api/sheets/' + ID).update({
              title: this.title,
              rows: this.rows
            }).then(function (resp) {
              console.log('saved ok.');
            }, function (resp) {
              alert('failed to save.');
            });
            */
            saveData('title', this);
            saveData('header', this, true);
            saveData('rows', this, true);
          },
          download: function () {
            var fileName = vm.title + '.xls';
            var a = document.createElement('a');
            a.setAttribute('href', 'data:text/xml,' + encodeURIComponent(makeXls(this.rows)));
            a.setAttribute('download', fileName);
            a.style.display = 'none';
            document.body.appendChild(a);
            a.click();
            document.body.removeChild(a);
          },
          focus: function (cell) {
            this.selectedRowIndex = cell.row;
            this.selectedColIndex = cell.col;
          },
          change: function (e) {
            var row = this.selectedRowIndex, col = this.selectedColIndex;
            var text = e.target.innerText;
            if (col > 0) {
              if (row > 0) {
                this.rows[row - 1][col].text = text;
              } else if (row === 0) {
                this.header[col].text = text;
              }
            } else if (col === 0 && row > 0) {
              if (/^\d+$/.test(text)) {
                var offset = parseInt(text) - row + 1;
                for (var i = 0; i < this.rows.length; ++i) {
                  this.rows[i][0].text = i + offset + '';
                }
              } else {
                e.target.innerText = this.rows[row-1][0].text;
              }
            }
          },
          setTitle: function (e) {
            var text = e.target.innerText;
            this.title = text;
            document.title = text;
          },
          selecting: function (cell) {
            return this.selectingFrom[1] > 0 && cell.col > 0 &&
              isBetween(cell.row, this.selectingFrom[0], this.selectingTo[0]) &&
              isBetween(cell.col, this.selectingFrom[1], this.selectingTo[1]);
          },
          startSelecting: function (cell) {
            if (cell.col > 0) {
              this.isSelecting = true;
              this.selectingFrom = this.selectingTo = [cell.row, cell.col];
            }
          },
          whileSelecting: function (cell) {
            if (this.isSelecting) {
              this.selectingTo = [cell.row, cell.col];
            }
          },
          copy: function (dataType) {
            var imin = Math.min(this.selectingFrom[0], this.selectingTo[0]);
            var imax = Math.max(this.selectingFrom[0], this.selectingTo[0]);
            var jmin = Math.min(this.selectingFrom[1], this.selectingTo[1]);
            var jmax = Math.max(this.selectingFrom[1], this.selectingTo[1]);
            var rows = [];
            for (var i = imin; i <= imax; ++i) {
              var row = [];
              for (var j = jmin; j <= jmax; ++j) {
                row.push(vm.rows[i - 1][j].text.trim());
              }
              rows.push(row);
            }

            var data;
            if (dataType == 'tsv') {
              data = rows.map(row => row.join('\t')).join('\n');
            } else if (dataType == 'csv') {
              data = rows.map(row => row.join(',')).join('\n');
            } else if (dataType == 'json') {
              data = rows.map(row => {
                obj = {}
                row.forEach((x,j) => {
                  obj[vm.header[jmin+j].text] = x;
                })
                return obj;
              });
              data = JSON.stringify(data);
            } else if (dataType == 'insert') {
              data = rows.map(row => `insert into ${this.title} (${
                row.map((x,j) => vm.header[jmin+j].text).join(',')
              }) values (${
                row.map(x => "'" + x + "'").join(',')
              });`).join('\n');
            } else if (dataType == 'update') {
              data = rows.map((row,i) => {
                return `update ${this.title} set ${
                  row.map((x,j) => vm.header[jmin+j].text + "='" + x + "'")
                  .join(',')
                } where id=${this.rows[imin+i-1][0].text};`
              }).join('\n');
            }
            console.log(data);

            function handler(event) {
              event.clipboardData.setData('text/plain', data);
              document.removeEventListener('copy', handler, true);
              event.preventDefault();
            }
            document.addEventListener('copy', handler, true);
            document.execCommand('copy');
            /*
            var textarea = $('<textarea>').val(data).appendTo(document.body);
            textarea[0].select();
            document.execCommand('copy');
            textarea.remove();
            */
          },
          paste: function (tsv) {
            tsv = tsv.split('\n').map(line => line.split('\t'));
            var imin = Math.min(this.selectingFrom[0], this.selectingTo[0]);
            var jmin = Math.min(this.selectingFrom[1], this.selectingTo[1]);
            for (var i = 0; i < tsv.length; ++i) {
              for (var j = 0; j < tsv[i].length; ++j) {
                vm.rows[imin+i-1][jmin+j].text = tsv[i][j];
              }
            }
          },
          align: function (alignType) {
            this.each(function (cell) {
              cell.align = alignType;
            });
          },
          each: function (callback) {
            var imin = Math.min(this.selectingFrom[0], this.selectingTo[0]);
            var imax = Math.max(this.selectingFrom[0], this.selectingTo[0]);
            var jmin = Math.min(this.selectingFrom[1], this.selectingTo[1]);
            var jmax = Math.max(this.selectingFrom[1], this.selectingTo[1]);
            for (var i = imin; i <= imax; ++i) {
              for (var j = jmin; j <= jmax; ++j) {
                callback(this.rows[i-1][j], i-imin, j-jmin);
              }
            }
          }
        },
      });
      window.vm = vm;

      // 粘贴事件
      document.addEventListener('paste', function (e) {
        var clipboardData = e.clipboardData;
        if (!(clipboardData && clipboardData.items)) return;
        for (var i = 0, len = clipboardData.items.length; i < len; i++) {
          var item = clipboardData.items[i];
          if (item.kind === 'string' && item.type == 'text/plain') {
            item.getAsString(function (str) {
              console.log(str);
              if (str.indexOf('\t') > -1) {
                vm.paste(str);
              }
            })
          }
        }
      });

      // 键盘事件
      document.onkeydown = function (e) {
        if (e.ctrlKey) {
          if (e.keyCode == 'C'.charCodeAt(0)) { // ctrl-c
            vm.copy('tsv');
          } else if (e.keyCode == 'X'.charCodeAt(0)) { // ctrl-x
            vm.copy('tsv');
            vm.each(cell => cell.text = '');
          }
        }
      }

      // 选取完毕
      document.onmouseup = function endSelecting() {
        if (vm.selectingFrom[1] == 0) return;
        vm.isSelecting = false;
      }

      // 离开时作提示
      window.onbeforeunload = function(event) {
          event.returnValue = 'unload';
      };
    });
  </script>
</head>

<body>
<div id="app">
  <header id="header" class="navbar navbar-static-top">
    <div class="container-fluid">
      <div class="pull-left">
        <span class="navbar-brand" style="color:#337ab7" contenteditable @blur="setTitle" v-text="title"></span>
      </div>
      <nav id="toolbar" class="pull-right">
        <!--
        <button id="cmd-paste" class="btn btn-default navbar-btn">
          <i class="glyphicon glyphicon-paste"></i> Paste</button>
        -->
        <div class="dropdown btn-group">
          <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true">
            <i class="glyphicon glyphicon-duplicate"></i> Copy
            <span class="caret"></span>
          </button>
          <ul class="dropdown-menu" aria-labelledby="dropdownMenu1">
            <li><a @click="copy('tsv')">TSV</a></li>
            <li><a @click="copy('csv')">CSV</a></li>
            <li><a @click="copy('json')">JSON</a></li>
            <li><a @click="copy('insert')">Insert SQL</a></li>
            <li><a @click="copy('update')">Update SQL</a></li>
          </ul>
        </div>
        <div class="btn-group">
          <button @click="load" class="btn btn-default navbar-btn" title="load from sessionStorage">
            <i class="glyphicon glyphicon-folder-open"></i> Load</button>
          <button @click="save" class="btn btn-default navbar-btn" title="save to sessionStorage">
            <i class="glyphicon glyphicon-floppy-disk"></i> Save</button>
          <button @click="download" class="btn btn-default navbar-btn">
            <i class="glyphicon glyphicon-save"></i> Download</button>
        </div>
        <div class="btn-group">
          <button @click="align('left')" class="btn btn-default navbar-btn">
            <i class="glyphicon glyphicon-align-left"></i></button>
          <button @click="align('center')" class="btn btn-default navbar-btn">
            <i class="glyphicon glyphicon-align-center"></i></button>
          <button @click="align('right')" class="btn btn-default navbar-btn">
            <i class="glyphicon glyphicon-align-right"></i></button>
        </div>
      </nav>
    </div>
  </header>

  <main id="main">
    <table id="sheet" class="table table-bordered">
      <thead>
        <tr>
          <th v-for="(cell,j) in header" :key="j" v-text="cell.text"
            :contenteditable="cell.contenteditable" @focus="focus(cell)" @blur="change"></th>
        </tr>
      </thead>
      <tbody>
        <tr v-for="(tr,i) in rows" :key="i">
          <td v-for="(cell,j) in tr" :key="j" v-text="cell.text"
            :style="{ textAlign: cell.align }" :class="{ selecting: selecting(cell) }"
            @mousedown="startSelecting(cell)" @mouseover="whileSelecting(cell)"
            :contenteditable="cell.contenteditable" @focus="focus(cell)" @blur="change">
          </td>
        </tr>
      </tbody>
    </table>
  </main>

  <footer id="footer" class="navbar">
    <div class="container-fluid">
      <nav>
        <p class="text-right" style="padding-top:5px">
          <a target="_blank" href="http://www.liaoxuefeng.com">Website</a> -
          <a target="_blank" href="https://github.com/michaelliao/learn-javascript">GitHub</a> -
          <a target="_blank" href="http://weibo.com/liaoxuefeng">Weibo</a>
          This JavaScript course is created by <a target="_blank" href="http://weibo.com/liaoxuefeng">@廖雪峰</a>.
          Code licensed <a target="_blank"
            href="https://github.com/michaelliao/learn-javascript/blob/master/LICENSE">Apache</a>.
        </p>
      </nav>
    </div>
  </footer>
</div>
</body>
</html>

